1 Imports System.Data.SqlClient
2 Imports Excel = Microsoft.Office.Interop.Excel
3
4 Public Class frmProfitAndLossReport
5
6 Dim a, b, c, d As Decimal
7 Sub Reset()
8 dtpDateFrom.Text = Today
9 dtpDateTo.Text = Today
10 End Sub
11 Private Sub btnReset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReset.Click
12 Reset()
13 End Sub
14
15
16 Private Sub btnClose_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
17 Me.Close()
18 End Sub
19
20 Private Sub Timer1_Tick(sender As System.Object, e As System.EventArgs) Handles Timer1.Tick
21 Cursor = Cursors.Default
22 Timer1.Enabled = False
23 End Sub
24
25 Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
26 Try
27 con = New SqlConnection(cs)
28 con.Open()
29 Dim ctn As String = "select InvoiceNo from InvoiceInfo where InvoiceDate between @d1 and @d2"
30 cmd = New SqlCommand(ctn)
31 cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "Date").Value = dtpDateFrom.Value.Date
32 cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "Date").Value = dtpDateTo.Value.Date
33 cmd.Connection = con
34 rdr = cmd.ExecuteReader()
35
36 If Not rdr.Read() Then
37 MessageBox.Show("Sorry..No record found", "", MessageBoxButtons.OK, MessageBoxIcon.Information)
38 If (rdr IsNot Nothing) Then
39 rdr.Close()
40 End If
41 Return
42 End If
43 Cursor = Cursors.WaitCursor
44 Timer1.Enabled = True
45 Dim rpt As New rptSales1 'The report you created.
46 Dim myConnection As SqlConnection
47 Dim MyCommand, MyCommand1 As New SqlCommand()
48 Dim myDA, myDA1 As New SqlDataAdapter()
49 Dim myDS As New DataSet 'The DataSet you created.
50 myConnection = New SqlConnection(cs)
51 MyCommand.Connection = myConnection
52 MyCommand1.Connection = myConnection
53 MyCommand.CommandText = "SELECT Customer.ID, Customer.Name, Customer.Gender, Customer.Address, Customer.City, Customer.State, Customer.ZipCode, Customer.ContactNo, Customer.EmailID, Customer.Remarks,Customer.Photo, InvoiceInfo.Inv_ID, InvoiceInfo.InvoiceNo, InvoiceInfo.InvoiceDate, InvoiceInfo.CustomerID , InvoiceInfo.GrandTotal, InvoiceInfo.TotalPaid, InvoiceInfo.Balance, Invoice_Product.IPo_ID, Invoice_Product.InvoiceID, Invoice_Product.ProductID, Invoice_Product.CostPrice, Invoice_Product.SellingPrice, Invoice_Product.Margin,Invoice_Product.Qty, Invoice_Product.Amount, Invoice_Product.DiscountPer, Invoice_Product.Discount, Invoice_Product.VATPer, Invoice_Product.VAT, Invoice_Product.TotalAmount, Product.PID,Product.ProductCode, Product.ProductName FROM Customer INNER JOIN InvoiceInfo ON Customer.ID = InvoiceInfo.CustomerID INNER JOIN Invoice_Product ON InvoiceInfo.Inv_ID = Invoice_Product.InvoiceID INNER JOIN Product ON Invoice_Product.ProductID = Product.PID where InvoiceDate between @d1 and @d2 order by InvoiceDate"
54 MyCommand.Parameters.Add("@d1", SqlDbType.DateTime, 30, "Date").Value = dtpDateFrom.Value.Date
55 MyCommand.Parameters.Add("@d2", SqlDbType.DateTime, 30, "Date").Value = dtpDateTo.Value.Date
56 MyCommand1.CommandText = "SELECT * from Company"
57 MyCommand.CommandType = CommandType.Text
58 MyCommand1.CommandType = CommandType.Text
59 myDA.SelectCommand = MyCommand
60 myDA1.SelectCommand = MyCommand1
61 myDA.Fill(myDS, "InvoiceInfo")
62 myDA.Fill(myDS, "Invoice_Product")
63 myDA.Fill(myDS, "Customer")
64 myDA.Fill(myDS, "Product")
65 myDA1.Fill(myDS, "Company")
66 con = New SqlConnection(cs)
67 con.Open()
68 Dim ct As String = "select ISNULL(sum(GrandTotal),0),ISNULL(sum(TotalPaid),0),ISNULL(sum(Balance),0) from InvoiceInfo where InvoiceDate between @d1 and @d2"
69 cmd = New SqlCommand(ct)
70 cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "Date").Value = dtpDateFrom.Value.Date
71 cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "Date").Value = dtpDateTo.Value.Date
72 cmd.Connection = con
73 rdr = cmd.ExecuteReader
74 If (rdr.Read()) Then
75 a = rdr.GetValue(0)
76 b = rdr.GetValue(1)
77 c = rdr.GetValue(2)
78
79 Else
80 a = 0
81 b = 0
82 c = 0
83 End If
84 con.Close()
85 con = New SqlConnection(cs)
86 con.Open()
87 Dim ct1 As String = "select ISNULL(sum(Margin),0) from InvoiceInfo,Invoice_Product where InvoiceInfo.Inv_ID=Invoice_Product.InvoiceID and InvoiceDate between @d1 and @d2"
88 cmd = New SqlCommand(ct1)
89 cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "Date").Value = dtpDateFrom.Value.Date
90 cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "Date").Value = dtpDateTo.Value.Date
91 cmd.Connection = con
92 rdr = cmd.ExecuteReader
93 If (rdr.Read()) Then
94 d = rdr.GetValue(0)
95 Else
96 d = 0
97 End If
98 con.Close()
99 rpt.SetDataSource(myDS)
100 rpt.SetParameterValue("p1", dtpDateFrom.Value.Date)
101 rpt.SetParameterValue("p2", dtpDateTo.Value.Date)
102 rpt.SetParameterValue("p3", a)
103 rpt.SetParameterValue("p4", b)
104 rpt.SetParameterValue("p5", c)
105 rpt.SetParameterValue("p6", d)
106 rpt.SetParameterValue("p7", Today)
107 frmReport.CrystalReportViewer1.ReportSource = rpt
108 frmReport.ShowDialog()
109 Catch ex As Exception
110 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
111 End Try
112 End Sub
113
114 Private Sub frmSalesReport_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
115
116 End Sub
117 End Class
